package me.baicai.weixin.identity.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.weixin.entity.order.MoneyLog;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

@Repository
public interface MoneyLogRepository extends JpaRepository<MoneyLog, Long>
{
    MoneyLog findMoneyLogByOrderid(String id);

    List<MoneyLog> getMoneyLogsByOrderid(String id);

    @Transactional
    void deleteAllByOrderid(String id);

    MoneyLog findMoneyLogById(Integer id);

    @Transactional
    void deleteMoneyLogById(Integer id);

    @Query(value = "select * from  money_log where targetId = ?1 and orderSrc = ?2 and created between ?3 and date_add(?4, interval 1 day)", nativeQuery = true)
    List<MoneyLog> getAllByTargetidAndOrdersrc(String openid, String src, String beginDate,
                                               String endDate);

    @Query(value = "select ifnull(SUM(amount),0) from  money_log where targetId = ?1 and orderSrc = ?2 and created between ?3 and date_add(?4, interval 1 day)", nativeQuery = true)
    Float getAmountTotal(String openid, String src, String beginDate, String endDate);

    @Query(value = "select o.id,o.amount as orderAmount,ifnull(m.amount,0) as amount,s.shopName, o.created, o.`end`, o.orderStatus from `order` o LEFT JOIN money_log m on m.orderId = o.id  and m.targetType = 'agent' and m.targetId = ?1 LEFT JOIN shop s on o.shopId = s.id where o.deviceSN = ?2", nativeQuery = true)
    List<JSONObject> deviceOrderInfo(String openid, String sn, Pageable pageable);

    @Query(value = "select d.id,count(o.id) as orderCount,sum(o.amount) as orderAmount,sum(m.amount) as commAmount from device d LEFT JOIN `order` o on d.sn = o.deviceSN LEFT JOIN money_log m on m.orderId = o.id where d.sn = ?2 and m.targetType = 'agent' and m.targetId = ?1 GROUP BY d.id", nativeQuery = true)
    JSONObject deviceAmountInfo(String openid, String sn);

    @Query(value = "select ifnull(SUM(m.amount),0) as amount from money_log m where m.targetId = ?1 and m.targetType = ?2 and m.orderSrc = '分成' and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(m.created) ", nativeQuery = true)
    float sanshitian(String openid, String type);

    @Query(value = "select ifnull(SUM(m.amount),0) as amount from money_log m where m.targetId = ?1 and m.targetType = ?2 and m.orderSrc = '分成' and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(m.created) ", nativeQuery = true)
    float qitian(String openid, String type);

    @Query(value = "select ifnull(SUM(m.amount),0) as amount from money_log m where m.targetId = ?1 and m.targetType = ?2 and m.orderSrc = '分成' and TO_DAYS(now()) - TO_DAYS(m.created) = 1 ", nativeQuery = true)
    float zuotian(String openid, String type);

    @Query(value = "select ifnull(SUM(m.amount),0) as amount from money_log m where m.targetId = ?1 and m.targetType = ?2 and m.orderSrc = '分成' and TO_DAYS(now()) = TO_DAYS(m.created) ", nativeQuery = true)
    float jintian(String openid, String type);

    @Query(value = "select cumulative as amount from account where openid = ?1", nativeQuery = true)
    float leiji(String openid, String type);

    @Query(value =
        "select DATE_FORMAT(a.click_date,'%m-%d') as click_date,ifnull(b.amount,0) as amount" +
        " from (" +
        "    SELECT curdate() as click_date" +
        "    union all" +
        "    SELECT date_sub(curdate(), interval 1 day) as click_date" +
        "    union all" +
        "    SELECT date_sub(curdate(), interval 2 day) as click_date" +
        "    union all" +
        "    SELECT date_sub(curdate(), interval 3 day) as click_date" +
        "    union all" +
        "    SELECT date_sub(curdate(), interval 4 day) as click_date" +
        "    union all" +
        "    SELECT date_sub(curdate(), interval 5 day) as click_date" +
        "    union all" +
        "    SELECT date_sub(curdate(), interval 6 day) as click_date" +
        ") a left join (" +
        "  select date(m.created) as datetime, sum(m.amount) as amount" +
        "  from money_log m where m.targetType = ?2 and m.targetId = ?1 and m.orderSrc = '分成'" +
        "  group by date(m.created)" +
        ") b on a.click_date = b.datetime order by a.click_date", nativeQuery = true)
    List<JSONObject> tubiao(String openid, String type);

    @Query(value = "select count(o.id) from `order` o where o.orderStatus = 2 and o.shopId in (select s.id from shop s where s.openid = 'oSMQR5XeFM0_5oly4958wpHB1FQE' ) and TO_DAYS(o.created) = TO_DAYS(NOW())", nativeQuery = true)
    Integer jintianOrder(String openid, String type);

    @Query(value = "select count(o.id) from `order` o where o.orderStatus = 2 and o.shopId in (select s.id from shop s where s.agentId = ?1 ) and TO_DAYS(o.created) = TO_DAYS(NOW())", nativeQuery = true)
    Integer jintianAgent(String openid, String type);

    @Query(value = "select ifnull(SUM(m.amount),0) as amount from money_log m where m.targetType = 'agent' and m.targetId = ?1 and m.orderSrc = '分成' and m.orderId in (select o.id from shop s LEFT JOIN `order` o  on o.shopId = s.id where s.agentId = ?1) and TO_DAYS(m.created) = TO_DAYS(NOW())", nativeQuery = true)
    float mineShop(String openid, String type);

    @Query(value = "select DATE_FORMAT(a.time,'%m-%d') as date , ifnull((select sum(m.amount) from money_log m where m.targetId = ?1 and date(m.created) = a.time and m.orderSrc = '分成' and m.moneyType = '收入'), 0) as amount " +
                   "                                     from (" +
                   "                                         SELECT date(now()) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 1 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 2 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 3 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 4 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 5 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 6 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 7 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 8 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 9 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 10 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 11 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 12 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 13 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 14 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 15 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 16 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 17 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 18 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 19 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 20 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 21 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 22 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 23 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 24 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 25 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 26 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 27 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 28 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 29 day) as time" +
                   "                                         union all" +
                   "                                         SELECT date_sub(date(now()), interval 30 day) as time" +
                   "                                     ) as a ORDER BY a.time", nativeQuery = true)
    List<JSONObject> month(String openid, String type);

    @Query(value = "select  DATE_FORMAT(m.created,'%H') hours,ifnull(SUM(m.amount),0) as amount from money_log m where m.targetId = ?1 and m.targetType = ?2 and m.orderSrc = '分成' and TO_DAYS(now()) - TO_DAYS(m.created) = 1 GROUP BY m.created", nativeQuery = true)
    List<JSONObject> yesTerDayList(String openid, String type);

    @Query(value = "select count(o.id) from shop s LEFT JOIN `order` o on o.shopId = s.id  where s.agentId = ?1", nativeQuery = true)
    Integer getOrderCountBySelf(String openid);

    @Query(value = "select count(m.amount) from shop s LEFT JOIN money_log m on m.shopid = s.id  where s.agentId = ?1", nativeQuery = true)
    Float getAmountBySelf(String openid);

    @Query(value = "select count(d.id) from shop s LEFT JOIN device d on d.shopId = s.id  where s.agentId = ?1", nativeQuery = true)
    Integer getDeviceCountBySelf(String openid);

    @Query(value = "select count(o.id) from shop s LEFT JOIN `order` o on o.shopId = s.id  where s.agentId in (select a.openid from agent a where a.parent = ?1)", nativeQuery = true)
    Integer getOrderCountByOther(String openid);

    @Query(value = "select sum(m.amount) from shop s LEFT JOIN money_log m on m.shopid = s.id  where m.targetId = ?1 and s.agentId in (select a.openid from agent a where a.parent = ?1)", nativeQuery = true)
    Float getAmountByOther(String openid);

    @Query(value = "select count(d.id) from shop s LEFT JOIN device d on d.shopId = s.id  where s.agentId in (select a.openid from agent a where a.parent = ?1)", nativeQuery = true)
    Integer getDeviceCountByOther(String openid);

    @Query(value = "SELECT o.id,o.amount as orderAmount,o.created,o.`end`,s.shopName,s.address,m.amount from `order` o LEFT JOIN shop s on o.shopId = s.id LEFT JOIN money_log m on m.orderId = o.id where s.agentId = ?1 and m.targetId = ?1", nativeQuery = true)
    List<JSONObject> getOrderBySelf(String openid, Integer page,Pageable pageable);

    @Query(value = "SELECT o.id,o.amount as orderAmount,o.created,o.`end`,s.shopName,s.address,m.amount from `order` o LEFT JOIN shop s on o.shopId = s.id LEFT JOIN money_log m on m.orderId = o.id where m.targetId = ?1 and s.agentId in (select a.openid from agent a where a.parent = ?1)", nativeQuery = true)
    List<JSONObject> getOrderByOther(String openid, Integer page,Pageable pageable);

    @Query(value = "select count(distinct orderId) from money_log where orderSrc = '分成' and targetId = ?1", nativeQuery = true)
    Integer getOrderCountByEmp(String openid);

    @Query(value = "select count(d.id) from shop s LEFT JOIN device d on d.shopId = s.id  where s.engId = ?1 or s.salesmanId = ?1 ", nativeQuery = true)
    Integer getDeviceCountByEmp(String openid);

    @Query(value = "SELECT o.id,o.amount as orderAmount,o.created,o.`end`,s.shopName,s.address,m.amount from `order` o LEFT JOIN shop s on o.shopId = s.id LEFT JOIN money_log m on m.orderId = o.id where s.engId = ?1 and m.targetId = ?1 or s.salesmanId = ?1 and m.targetId = ?1", nativeQuery = true)
    List<JSONObject> getOrderByEmp(String openid, Pageable pageable);

    @Query(value = "select ifnull(SUM(m.amount),0) as amount from money_log m where m.targetType = 'shop' and m.targetId = ?1 and m.orderSrc = '分成' and m.orderId in (select o.id from shop s LEFT JOIN `order` o  on o.shopId = s.id where s.openid = ?1) and TO_DAYS(m.created) = TO_DAYS(NOW())", nativeQuery = true)
    float mineShopShop(String openid, String type);
}
